﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_WEIGHT_TRAINING_Select_Paging')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_WEIGHT_TRAINING_Select_Paging
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_WEIGHT_TRAINING_Select_Paging
**	Desc : 운동수행관리를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_FIT_WEIGHT_TRAINING_Select_Paging 0, 20, '2014-05-01', '2014-05-31', '', '', 'ko'
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING.cs
**	Program ID : Fit1011m
**	Auth : 송시명
**	Date : 2014-05-21
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_WEIGHT_TRAINING_Select_Paging]
@li_st_page int,
@li_ed_page int,
@ls_training_dt_from nvarchar(10),
@ls_training_dt_to nvarchar(10),
@ls_training_part nvarchar(max),
@ls_training_type_id nvarchar(max),
@ls_location nvarchar(100),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @training_parts nvarchar(max)
IF @ls_training_part = ''
BEGIN
    SET @training_parts = '%'
END
ELSE
BEGIN
    SET @training_parts = @ls_training_part
END

DECLARE @training_type_ids nvarchar(max)
IF @ls_training_type_id = ''
BEGIN
    SET @training_type_ids = '%'
END
ELSE
BEGIN
    SET @training_type_ids = @ls_training_type_id
END

SELECT Seq,
       Training_Id,
       Training_Dt,
       Day_Of_Week,
       Start_Hour,
       Start_Minute,
       End_Hour,
       End_Minute,
       Training_Time,
       Training_Part,
       Training_Part_Nm,
       CONVERT(DECIMAL(10,2),Total_Weight) AS Total_Weight,
       CONVERT(INT,Total_Count) AS Total_Count,
	   CONVERT(INT,Total_Set) AS Total_Set,
       Location,
       Result
  FROM (
       SELECT ROW_NUMBER() OVER(ORDER BY Training_Dt DESC) AS Rnum,
              Seq,
              Training_Id,
              Training_Dt,
              Day_Of_Week,
              Start_Hour,
              Start_Minute,
              End_Hour,
              End_Minute,
              Training_Time,
              Training_Part,
              Training_Part_Nm,
              Total_Weight,
              Total_Count,
			  Total_Set,
              Location,
              Result
         FROM (
              SELECT DISTINCT
                     A.Seq,
                     A.Training_Id,
                     A.Training_Dt,
                     CASE @ls_lang_set WHEN 'ko' THEN D.Day_K_Nm ELSE D.Day_E_Nm END Day_Of_Week,
                     A.Start_Hour,
                     A.Start_Minute,
                     A.End_Hour,
                     A.End_Minute,
                     A.Start_Hour + ':' + A.Start_Minute + ' ~ ' + A.End_Hour + ':' + A.End_Minute
                     + ' (' + CONVERT(VARCHAR, DATEDIFF(MINUTE, A.Start_Hour + ':' + A.Start_Minute, A.End_Hour + ':' + A.End_Minute)) + ')' AS Training_Time,
                     dbo.ufn_UPDMS_Get_Training_Info_By_Training_Id(A.Training_Id, C.Training_Part, 'TRAINING_PART', @ls_lang_set) AS Training_Part,
                     dbo.ufn_UPDMS_Get_Training_Info_By_Training_Id(A.Training_Id, C.Training_Part, 'TRAINING_PART_NM', @ls_lang_set) AS Training_Part_Nm,
                     dbo.ufn_UPDMS_Get_Training_Info_By_Training_Id(A.Training_Id, C.Training_Part, 'TRAINING_WEIGHT', @ls_lang_set) AS Total_Weight,
                     dbo.ufn_UPDMS_Get_Training_Info_By_Training_Id(A.Training_Id, C.Training_Part, 'TRAINING_COUNT', @ls_lang_set) AS Total_Count,
					 dbo.ufn_UPDMS_Get_Training_Info_By_Training_Id(A.Training_Id, C.Training_Part, 'TRAINING_SET', @ls_lang_set) AS Total_Set,
                     A.Location,
                     '<div title="결과" class="sbtn sbtn_report" onclick="goWeightTrainingResultPage(''' + A.Training_Id + ''');"></div>' AS Result
                FROM UPDMS_FIT_WEIGHT_TRAINING A WITH(NOLOCK)
                JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
                JOIN UPDMS_FIT_TRAINING_TYPE C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
                JOIN UPDMS_MST_DATE D WITH(NOLOCK) ON A.Training_Dt = D.Dt
               WHERE A.Training_Dt >= @ls_training_dt_from
                 AND A.Training_Dt <= @ls_training_dt_to
                 AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
                     OR C.Training_Part LIKE @training_parts
                     )
                 AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))
                     OR C.Training_Type_Id LIKE @training_type_ids
                     )
                 AND A.Location LIKE '%' + @ls_location + '%'
              ) z
       ) a
 WHERE a.Rnum > @li_st_page AND a.Rnum <= @li_ed_page

GO